
if exists (select 1 from sysobjects where name = 'vw_person' and type = 'V')
begin
   drop view vw_person
   print 'View: vw_person deleted ...'
end
go
create view vw_person
as
   select 
      prsid  = p.PrsId,
   nachname  = p.Nachname,
    vorname  = p.Vorname, 
    strasse  = p.Strasse + ' ' + p.Strassennr,
   postfach  = p.Postfach,
        PLZ  = p.PLZ,
        ort  = p.Ort,
     telnrp  = p.TelnrP,
     telnrn  = p.TelnrN,
      email  = p.Email,
    prozent  = a.Prozent, 
abteilungid  = abt.AbteilungID,
  abteilung  = abt.Name,
anstellungid = ans.AnstellungID,
 anstellung  = ans.Name
   from Person p 
  inner join PersonalAbteilung pa 
     on p.PrsId = pa.PrsId 
and pa.Bis = (select max(pa2.Bis) from PersonalAbteilung pa2 where pa2.PrsId = p.PrsId)
   left outer join Abteilung abt
     on abt.AbteilungID = pa.AbteilungID
  inner join Arbeitspensum a 
     on a.PrsID = p.PrsId 
--    and a.status = 1 
--and a.von <= @datum 
and a.Bis = (select max(Bis) from Arbeitspensum ab where ab.PrsID = p.PrsId)
   left outer join PersonAnstellung pans
     on pans.PrsID = p.PrsId
   left outer join Anstellung ans
     on ans.AnstellungID = pans.AnstellungID
where p.PersonTyp = 1
go
print 'View: vw_person done ...'
go

grant select on vw_person to prsadmins with grant option
go
grant select on vw_person to prsusers
go

